package database.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import twitter4j.Status;
import twitter4j.internal.org.json.JSONArray;
import twitter4j.internal.org.json.JSONException;

import database.objects.DatabaseObject;
import database.objects.MessageOwner;

/**
 * Model for the MessageOwner model.
 * 
 * @author Project group 9
 * 
 */
public class MessageOwnerModel extends Model {

	/**
	 * Constructor for MessageOwner. Connection and statement are initialized in
	 * the super Model.
	 * 
	 * @param connection
	 *            A database connection
	 */
	public MessageOwnerModel(Connection con) {
		super(con);
	}

	/**
	 * Method to get one MessageOwner based on his id
	 * 
	 * @param id
	 *            The id you search for
	 * @return MessageOwner object
	 * @throws SQLException
	 */
	public MessageOwner byId(long id) {
		return (MessageOwner) load("id", id, null).get(0);
	}

	/**
	 * Gets an owner based upon his id from twitter.
	 * 
	 * @param twitterId
	 *            The id that is searched for.
	 * @return MessageOwner object
	 */
	public MessageOwner byTwitterId(long twitterId) {
		return (MessageOwner) load("twitter_id", twitterId, null).get(0);
	}

	/**
	 * Method to select something from the message_owner table.
	 * 
	 * @param column
	 *            The column where you want to search on.
	 * @param id
	 *            The id you want to get from that column
	 * @return currently one MessageOwner object.
	 * @throws SQLException
	 */
	@Override
	public ArrayList<DatabaseObject> load(String column, long id, String value) {
		ArrayList<DatabaseObject> messageOwners = new ArrayList<DatabaseObject>();
		try {
			PreparedStatement pst = con
					.prepareStatement("SELECT * FROM `message_owner` WHERE `"
							+ column + "` = ? ");
			if (id != -1) {
				pst.setLong(1, id);
			} else {
				pst.setString(1, value);
			}
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					messageOwners.add(new MessageOwner(rs.getLong("id"), rs
							.getLong("twitter_id"), rs.getString("name"), rs
							.getString("profile_image_url")));
				} while (rs.next());
			} else {
				messageOwners.add(new MessageOwner());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return messageOwners;
	}
	
	public ArrayList<HashMap<String, String>> getTopOwners(int amount, JSONArray keywords) throws JSONException {
		ArrayList<HashMap<String, String>> topOwners = new ArrayList<HashMap<String, String>>();
		String sqlKeywords = "";
		
		try {
			if(keywords.length() != 0) {
				for(int i = 0; i < keywords.length(); i++){
					sqlKeywords += " AND text LIKE '%" + keywords.get(i) +"%'";
				}
			}
			
			PreparedStatement pst = con.prepareStatement(
				"SELECT name, count(owner) as count" +
				" FROM message " +
				" INNER JOIN message_owner ON message.owner = message_owner.id" +
				"" + sqlKeywords +
				" AND created_at > ?" +
				" GROUP BY owner " +
				" ORDER BY count DESC " +
				" LIMIT " + amount
			);
			
			Calendar today = Calendar.getInstance();
			today.add(Calendar.DATE, -1);
			java.sql.Date yesterday = new java.sql.Date(today.getTimeInMillis());

			pst.setString(1, yesterday.toString());
			
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					HashMap<String, String> owner = new HashMap<String, String>();
					owner.put("account" , rs.getString("name"));
					owner.put("count" , "" + rs.getString("count"));
					topOwners.add(owner);
				} while (rs.next());
			} else {
				topOwners.add(new HashMap<String, String>());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return topOwners;
	}

	/**
	 * Saves a twitter user into the database
	 * 
	 * @param Status
	 *            This is the status of a tweet.
	 */
	public void save(Status status) {
		try {
			PreparedStatement pst = con
					.prepareStatement("INSERT INTO message_owner (`twitter_id`, `name`, `profile_image_url`) VALUES (?, ?, ?)");
			pst.setLong(1, status.getUser().getId());
			pst.setString(2, status.getUser().getName());
			pst.setString(3, status.getUser().getProfileImageURL().toString());
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
